/*
    This file is part of SisIB - Sistema de Identificação Biométrica.

    Copyright (C) 2014  Thiago Azevedo Alves.
    
    SisIB is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    SisIB is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with SisIB.  If not, see <http://www.gnu.org/licenses/>.
 */


package sisib;


import com.griaule.grfingerjava.Template;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.Vector;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JOptionPane;

/**
 *
 * @author Thiago Azevedo Alves
 */
public class Database {
    public Connection conn = null;
    public Statement statment = null;
    public PreparedStatement preparedStatement = null;
    public ResultSet resultSet = null;
    
    //realiza a coneexão no banco de dados;
    public void connect(){
        try{
            Class.forName("com.mysql.jdbc.Driver"); //carrega o driver do mysql
            String url = "jdbc:mysql://localhost:3306/unimed_biom_teste"; //acessa a tablea mysql "unimed_biom_teste" no localhost
            String usuario = "root";
            String senha = null;
            conn = DriverManager.getConnection(url, usuario, senha); //conecta no banco de dados MySql
        }catch (ClassNotFoundException | SQLException e){
            e.getLocalizedMessage();
        }
    }
    
    //Slava um usuario na tabela
    
    //Modificar Método de Inserção)
    //Inserir na tabela func o funcionario x (select cad_func.id as Y where cad_func.nome = x)
    //insert into func values(Y, x, template)
    
    public void Salva( int id, String nome, String uni, byte[] template){
                
        try {
            preparedStatement = conn.prepareStatement("insert into func values(?, ?, ?, ?)"); //prepara os argumentos;
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, nome);
            preparedStatement.setString(3, uni);  
            preparedStatement.setBytes(4, template);              
            
                        
            preparedStatement.executeUpdate(); //executa o update na tabela
        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }
    
    
    
    public void acessa(String nFunc, int idEvt){
        
        try {
            preparedStatement = conn.prepareStatement("insert into func_evt values(default, ?, ?, ?, ?)"); //prepara os argumentos;
            preparedStatement.setString(1, nFunc);
            preparedStatement.setInt(2, idEvt);
            preparedStatement.setDate(3, Date.valueOf(LocalDate.now()));
            preparedStatement.setTime(4, Time.valueOf(LocalTime.now()));
            preparedStatement.executeUpdate(); //executa o update na tabela
        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }
    
    public Connection getConnection() throws SQLException, ClassNotFoundException{
        String url = "jdbc:mysql://localhost:3306/unimed_biom_teste"; //acessa a tablea mysql "unimed_biom_teste" no localhost
        String usuario = "root";
        String senha = null;
        return DriverManager.getConnection(url, usuario, senha);
    }
    
    public int getEvt(String descr) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select id from evt where descr=?");
            preparedStatement.setString(1, descr);
            resultSet = preparedStatement.executeQuery();
            int n;
            while(resultSet.next()){
                n = resultSet.getInt("id");
                return n;
            }
        } catch (Exception e) {
            e.toString();
        }
        return -1;
    }
    
    //Retorna um Vetor com todos os templates cadastrados no banco de dados;
    @SuppressWarnings("empty-statement")
    public Vector getTemplateVector() throws SQLException{
        Vector v = null;
        Template t = null;
            try {
            preparedStatement = conn.prepareStatement("select * from func"); //prepara para selecionar todos os campos da tabela;
            resultSet = preparedStatement.executeQuery(); //resultSet recebe todos os templates
            
            //enquanto possui templates no resultSet ele adiciona em um vetor v;
            if(resultSet.next()){ 
                t = new Template(resultSet.getBytes("temp"));
                t.setData(resultSet.getBytes("temp"));
                v = new Vector();
                v.add(0, t.getData());
            }
            while(resultSet.next()){
                t = new Template(resultSet.getBytes("temp"));
                v.add(0, t.getData());
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return v;
    }
    
    
    //Retorna o Template do usuário id = 0;
    public byte[] getTemplateIni() throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            Template t = new Template(resultSet.getBytes("temp"));        
            return t.getData();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    
    //Retorna o template de id = i;
    public byte[] getTemplate(int i) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            Template t = new Template();        
            int j = 0;
            while(resultSet.next()){
                if (i == j){
                    t.setData(resultSet.getBytes("temp"));
                    return t.getData();
                }else{
                    j++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public int getId(int i) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            int n;        
            int j = 0;
            while(resultSet.next()){
                if (i == j){
                    n = resultSet.getInt("id");
                    return n;
                }else{
                    j++;
                }
            }
        } catch (Exception e) {
            e.toString();
        }
        return -1;
    }
    
    public int getId(String nome) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select id from cad_func where nome=?");
            preparedStatement.setString(1, nome);
            resultSet = preparedStatement.executeQuery();
            int n;        
            int j = 0;
            while(resultSet.next()){
                n = resultSet.getInt("id");
                return n;
            }
        } catch (Exception e) {
            e.toString();
        }
        return -1;
    } 
    
    public String getNome(int i) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            String n = new String();        
            int j = 0;
            while(resultSet.next()){
                if (i == j){
                    n = resultSet.getString("nome");
                    return n;
                }else{
                    j++;
                }
            }
        } catch (Exception e) {
            e.toString();
        }
        return null;
    }
    
    public String getUnimed(int i) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            String n = new String();        
            int j = 0;
            while(resultSet.next()){
                if (i == j){
                    n = resultSet.getString("unimed");
                    return n;
                }else{
                    j++;
                }
            }
        } catch (Exception e) {
            e.toString();
        }
        return null;
    }
    
    public String getUnimed(String nome) throws SQLException{
        try {
            preparedStatement = conn.prepareStatement("select unimed from cad_func where nome=?");
            preparedStatement.setString(1, nome);
            resultSet = preparedStatement.executeQuery();
            String n = new String();        
            int j = 0;
            while(resultSet.next()){
                return resultSet.getString("unimed");
            }
        } catch (Exception e) {
            e.toString();
        }
        return null;
    }
    
    public int getLenght(){
        int i = 0;
        try {
            preparedStatement = conn.prepareStatement("select * from func");
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                i++;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }
    
    public DefaultComboBoxModel getListaNomes(){
        DefaultComboBoxModel modelo = new DefaultComboBoxModel();
        try{
            preparedStatement = conn.prepareStatement("select nome from cad_func");
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                modelo.addElement(resultSet.getString("nome"));
            }
        }catch(SQLException e){
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        return modelo;
    }
    
    public DefaultComboBoxModel getListaEvt(){
        DefaultComboBoxModel modelo = new DefaultComboBoxModel();
        try{
            preparedStatement = conn.prepareStatement("select descr from evt");
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                modelo.addElement(resultSet.getString("descr"));
            }
        }catch(SQLException e){
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        return modelo;
    }
    
}